namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManagePreference
    {
        public static SqlDataReader GetCustomerPreferenceInfo(Guid customerId)
        {
            string strSQL = "SELECT TB_PreferencesList.PreferenceNameCn,TB_PreferencesList.PreferenceNameEn,TB_PreferencesValueList.PreferenceValueNameCn,TB_PreferencesValueList.PreferenceValueNameEn,TB_PreferencesLevel.PreferenceLevelNameCn,TB_PreferencesLevel.PreferenceLevelNameEn,TB_CustomerPreference.OtherPre,TB_CustomerPreference.CustomerPreferenceId,TB_CustomerPreference.PreferenceId FROM TB_PreferencesList,TB_PreferencesValueList,TB_PreferencesLevel,TB_CustomerPreference where TB_CustomerPreference.PreferenceId = TB_PreferencesList.PreferenceId  and TB_CustomerPreference.PreferenceValueId = TB_PreferencesValueList.PreferenceValueId and TB_CustomerPreference. PreferenceLevelId= TB_PreferencesLevel.PreferenceLevelId and  CustomerId=@CustomerId order by TB_CustomerPreference.PreferenceId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetPreferenceLevel(Guid preferenceId)
        {
            string strSQL = "Select * from TB_PreferencesLevel where PreferenceId=@PreferenceId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetPreferenceType()
        {
            string strSQL = "Select * from TB_PreferencesList ORDER BY PreferenceNameCn";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetPreferenceType(Guid areaId)
        {
            string strSQL = "Select * from TB_PreferencesList where AreaId=@AreaId ORDER BY PreferenceNameCn";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static PreferenceKind GetPreferenceTypeViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            PreferenceKind preferenceKind = new PreferenceKind();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_PreferencesList where PreferenceId = @PreferenceId";
            MyDB.AddParameters(id, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                preferenceKind.PreferenceId = MyReader.GetGuid(0);
                preferenceKind.PreferenceNameCn = MyReader.GetString(1);
                preferenceKind.PreferenceNameEn = MyReader.GetString(2);
            }
            MyDB.DBClose();
            return preferenceKind;
        }

        public static SqlDataReader GetPreferenceValue(Guid preferenceId)
        {
            string strSQL = "Select * from TB_PreferencesValueList where PreferenceId=@PreferenceId";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static PreferenceValueLevel GetPreferenceValueLevelViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            PreferenceValueLevel preferenceValueLevel = new PreferenceValueLevel();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_PreferencesLevel where PreferenceLevelId = @PreferenceLevelId";
            MyDB.AddParameters(id, "PreferenceLevelId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                preferenceValueLevel.PreferenceValueLevelId = MyReader.GetGuid(0);
                preferenceValueLevel.PreferenceValueLevelNameCn = MyReader.GetString(2);
                preferenceValueLevel.PreferenceValueLevelNameEn = MyReader.GetString(3);
            }
            MyDB.DBClose();
            return preferenceValueLevel;
        }

        public static PreferenceValue GetPreferenceValueViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            PreferenceValue preferenceValue = new PreferenceValue();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_PreferencesValueList where PreferenceValueId = @PreferenceValueId";
            MyDB.AddParameters(id, "PreferenceValueId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                preferenceValue.PreferenceValueId = MyReader.GetGuid(0);
                preferenceValue.PreferenceValueNameCn = MyReader.GetString(2);
                preferenceValue.PreferenceValueNameEn = MyReader.GetString(3);
            }
            MyDB.DBClose();
            return preferenceValue;
        }

        public static void InsertNewCustomerPreferenceInfo(Guid customerId, Guid preferenceId, Guid preferenceValueId, Guid preferenceLevelId, string otherPre)
        {
            Guid customerPreferenceId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerId, "CustomerId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceValueId, "PreferenceValueId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceLevelId, "PreferenceLevelId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(otherPre, "OtherPre", SqlDbType.VarChar);
            MyDB.AddParameters(customerPreferenceId, "CustomerPreferenceId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_CustomerPreference (CustomerId,PreferenceId,PreferenceValueId,PreferenceLevelId,OtherPre,CustomerPreferenceId) values (@CustomerId,@PreferenceId,@PreferenceValueId,@PreferenceLevelId,@OtherPre,@CustomerPreferenceId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewPreferenceLevel(Guid preferenceId, string preferenceLevelNameCn, string preferenceLevelNameEn)
        {
            Guid preferenceLevelId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceLevelId, "PreferenceLevelId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceLevelNameCn, "PreferenceLevelNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(preferenceLevelNameEn, "PreferenceLevelNameEn", SqlDbType.VarChar);
            string strSQL = "Insert into TB_PreferencesLevel (PreferenceLevelId,PreferenceId,PreferenceLevelNameCn,PreferenceLevelNameEn) values (@PreferenceLevelId,@PreferenceId,@PreferenceLevelNameCn,@PreferenceLevelNameEn)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void InsertNewPreferenceType(string preferenceNameCn, string preferenceNameEn, Guid areaId)
        {
            Guid preferenceId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceNameCn, "PreferenceNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(preferenceNameEn, "PreferenceNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_PreferencesList (PreferenceId,PreferenceNameCn,PreferenceNameEn,AreaId) values (@PreferenceId,@PreferenceNameCn,@PreferenceNameEn,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
            InsertNewPreferenceValue(preferenceId, "", "");
            InsertNewPreferenceLevel(preferenceId, "", "");
        }

        public static void InsertNewPreferenceValue(Guid preferenceId, string preferenceValueNameCn, string preferenceValueNameEn)
        {
            Guid preferenceValueId = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceValueId, "PreferenceValueId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(preferenceValueNameCn, "PreferenceValueNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(preferenceValueNameEn, "PreferenceValueNameEn", SqlDbType.VarChar);
            string strSQL = "Insert into TB_PreferencesValueList (PreferenceValueId,PreferenceId,PreferenceValueNameCn,PreferenceValueNameEn) values (@PreferenceValueId,@PreferenceId,@PreferenceValueNameCn,@PreferenceValueNameEn)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistPreferenceIdInPreferenceLevel(Guid PreferenceId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(PreferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_PreferencesLevel where PreferenceId = @PreferenceId and PreferenceLevelNameCn <> ''";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static bool IsExistPreferenceIdInPreferenceValue(Guid PreferenceId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(PreferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_PreferencesValueList where PreferenceId = @PreferenceId and PreferenceValueNameCn<> ''";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveCustomerPreferenceInfoViaId(Guid customerPreferenceId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(customerPreferenceId, "CustomerPreferenceId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerPreference where CustomerPreferenceId = @CustomerPreferenceId and CustomerPreferenceId <> '00000000-0000-0000-0000-000000000000'";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerPreferenceLevelViaId(Guid preferenceLevelId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceLevelId, "PreferenceLevelId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_PreferencesLevel where PreferenceLevelId = @PreferenceLevelId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemoveCustomerPreferenceValueViaId(Guid preferenceValueId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceValueId, "PreferenceValueId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_PreferencesValueList where PreferenceValueId = @PreferenceValueId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void RemovePreferenceTypeViaId(Guid preferenceId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceId, "PreferenceId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_PreferencesList where PreferenceId = @PreferenceId and PreferenceId <> '00000000-0000-0000-0000-000000000000'";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdatePreferenceType(Guid id, string preferenceNameCn, string preferenceNameEn)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceNameCn, "PreferenceNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(preferenceNameEn, "PreferenceNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "PreferenceId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_PreferencesList set PreferenceNameCn = @PreferenceNameCn,PreferenceNameEn = @PreferenceNameEn where PreferenceId = @PreferenceId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdatePreferenceValue(Guid id, string preferenceValueNameCn, string preferenceValueNameEn)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceValueNameCn, "PreferenceValueNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(preferenceValueNameEn, "PreferenceValueNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "PreferenceValueId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_PreferencesValueList set PreferenceValueNameCn = @PreferenceValueNameCn,PreferenceValueNameEn = @PreferenceValueNameEn where PreferenceValueId = @PreferenceValueId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdatePreferenceValueLevel(Guid id, string preferenceLevelNameCn, string preferenceLevelNameEn)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(preferenceLevelNameCn, "PreferenceLevelNameCn", SqlDbType.VarChar);
            MyDB.AddParameters(preferenceLevelNameEn, "PreferenceLevelNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "PreferenceLevelId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_PreferencesLevel set PreferenceLevelNameCn = @PreferenceLevelNameCn,PreferenceLevelNameEn = @PreferenceLevelNameEn where PreferenceLevelId = @PreferenceLevelId";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

